﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KD.Sms.DAL.SqlLite
{
    internal sealed partial class Main
    {


        /// <summary>
        /// 查询获取需要上传的记录
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable DoQuerySamplingForUpload(bool isAll)
        {
            _strSql = string.Format(@"select Sampling_id,Car_No,'' as FLowid, '' as Device,TotalLength,CarriageLength,CarriageWidth,CarriageHeight,CarriagePostions,batchdate,kind_name,Material_Name,Supplier_Name,Unit_name,
                        Sample_Pail,Pos_count,LayoutType,Sampling_Time,Sampling_Time_End,Sampling_State,SampleWeight,SampleRfid,replace(replace(replace(SamplingPos,'),(','*'),')',''),'(','') as SamplingPos,
                        Img_Begin,Img_End,Sparestr1 from v_SamplingForUpload
                        order by Sampling_id asc", isAll ? string.Empty : " limit 50 ");
            return SQLiteHelper.GetDataTable(_strSql);
        }

        /// <summary>
        /// 将最近100条记录中 桶号为空的数据 替补
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable CompensationSampleRfid()
        {
            _strSql = $@"SELECT * FROM (
												SELECT Sampling_Id,SamplingCode,SampleRfid FROM S_Samplings WHERE SampleRfid = 0 
												UNION
												SELECT Sampling_Id,SamplingCode,SampleRfid FROM S_Samplings WHERE SampleRfid = ''
												UNION
												SELECT Sampling_Id,SamplingCode,SampleRfid FROM S_Samplings WHERE SampleRfid IS NULL 
												) LIMIT 100";
            return SQLiteHelper.GetDataTable(_strSql);
        }

        /// <summary>
        /// 将数据同步到本地
        /// </summary>
        /// <param name="lpr"></param>
        /// <param name="flowid"></param>
        /// <param name="tlen"></param>
        /// <param name="twidth"></param>
        /// <param name="len"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="postion"></param>
        /// <param name="mine"></param>
        /// <param name="material"></param>
        /// <param name="supplier"></param>
        /// <param name="transport"></param>
        /// <returns></returns>
        public bool DoSaveDataLocal(string lpr, string flowid, decimal tlen, decimal twidth, decimal len, decimal width, decimal height, string postion, string mine, string material, string supplier, string transport,string sparestr1="")
        {
            try
            {
                List<string> list = new List<string>();
                var _strSql = string.Empty;
                var _strKindId = "K" + DateTime.Now.ToString("yyMMddHHmmssfff");
                var _strMaterialId = "M" + DateTime.Now.ToString("yyMMddHHmmssfff");
                var _strSupplierId = "S" + DateTime.Now.ToString("yyMMddHHmmssfff");
                var _strUnitId = "U" + DateTime.Now.ToString("yyMMddHHmmssfff");
                var _strBatchCode = DoQueryCodes(DateTime.Now);
                list.Add(string.Format("delete from S_CarInfo where CarNum ='{0}'", lpr));
                list.Add(string.Format("delete from S_CarBatch where CarNum ='{0}' and Status = 0", lpr));
                list.Add(string.Format("insert into S_Kind(Kind_Id,Kind_Name)select '{0}','{1}' where not exists(select 1 from S_Kind where Kind_Name = '{1}')", _strKindId, mine));
                list.Add(string.Format("insert into S_Material(Material_Id,Material_Name)select '{0}','{1}' where not exists(select 1 from S_Material where Material_Name = '{1}')", _strMaterialId, material));
                list.Add(string.Format("insert into S_Supplier(Supplier_Id,Supplier_Name)select '{0}','{1}' where not exists(select 1 from S_Supplier where Supplier_Name = '{1}')", _strSupplierId, supplier));
                list.Add(string.Format("insert into S_TrafficUnit(Unit_ID,Unit_Name)select '{0}','{1}' where not exists(select 1 from S_TrafficUnit where Unit_Name = '{1}')", _strUnitId, transport));
                list.Add(string.Format("insert into S_CarInfo(CarNum,CardNo,TotalLength,TotalWidth,CLength,CWidth,CHeight,Postion,Sparestr1)values('{0}','{1}',{2},{3},{4},{5},{6},'{7}','{8}')", lpr, flowid, tlen, twidth, len, width, height, postion, sparestr1));
                list.Add(string.Format(@"insert into S_Batch(Codes,Kind_ID,Material_Id,Supplier_Id,Unit_Id,BatchDate)
                                                select '{0}','{1}','{2}','{3}','{4}','{5}' 
                                                where not exists (select 1 from S_Batch where Kind_ID = '{1}' and Material_Id = '{2}' and Supplier_Id ='{3}' and Unit_Id = '{4}' and BatchDate ='{5}')",
                                                _strBatchCode, mine, material, supplier, transport, DateTime.Now.ToString("yyyy-MM-dd")));
                list.Add(string.Format("insert into S_CarBatch(CarNum,Codes)select '{0}',Codes from S_Batch where Kind_ID = '{1}' and Material_Id = '{2}' and Supplier_Id ='{3}' and Unit_Id = '{4}' and BatchDate ='{5}'",
                    lpr, mine, material, supplier, transport, DateTime.Now.ToString("yyyy-MM-dd")));
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoSaveDataLocal()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 标记数据已上传
        /// </summary>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public bool DoMarkUploadTarget(int samplingid)
        {
            _strSql = string.Format("update s_samplings set UploadStatus = 1,UploadTime = '{0}' where Sampling_Id = {1} ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), samplingid);
            return SQLiteHelper.ExecuteNonQuery(_strSql, null) > 0;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string DoQueryCodes(DateTime date)
        {
            _strSql = string.Format("select max(Codes)+1 as Codes from s_Batch where batchDate='{0}'", date.ToString("yyyy-MM-dd"));
            object obj = SQLiteHelper.ExecuteScalar(_strSql, null);
            if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
            {
                return obj.ToString();
            }
            else
                return date.ToString("yyMMdd") + "01";
        }

        /// <summary>
        /// 获取车辆采样信息（根据车牌号码（湘A***））
        /// </summary>
        /// <param name="carnum">车牌号码（湘A***）</param>
        /// <returns></returns>
        public Entity.SamplingInfo DoSendRequest(string carnum)
        {
            Entity.SamplingInfo _modeResult = new Entity.SamplingInfo();
            try
            {
                _strSql = string.Format(" select * from v_CarBatch where  CarNum  = '{0}' ", carnum);
                System.Data.DataTable _tabSource = SQLiteHelper.GetDataTable(_strSql);
                if (_tabSource != null && _tabSource.Rows.Count > 0)
                {
                    if (_tabSource.Rows.Count == 1)
                    {
                        _modeResult.En_Car = carnum;
                        _modeResult.En_Batch = Convert.ToString(_tabSource.Rows[0]["Codes"].ToString());
                        _modeResult.En_PrimaryKey = Convert.ToString(_tabSource.Rows[0]["CardNo"].ToString());
                        _modeResult.En_ProcessId = string.Empty;
                        _modeResult.En_TotleLength = Convert.ToDouble(_tabSource.Rows[0]["TotalLength"].ToString());
                        _modeResult.En_TotleWidth = Convert.ToDouble(_tabSource.Rows[0]["TotalWidth"].ToString());
                        _modeResult.En_Length = Convert.ToDouble(_tabSource.Rows[0]["CLength"].ToString());
                        _modeResult.En_Width = Convert.ToDouble(_tabSource.Rows[0]["CWidth"].ToString());
                        _modeResult.En_Height = Convert.ToDouble(_tabSource.Rows[0]["CHeight"].ToString());
                        _modeResult.En_Postions = _tabSource.Rows[0]["Postion"].ToStringEx();
                        _modeResult.En_Mine = Convert.ToString(_tabSource.Rows[0]["Kind_ID"].ToString());
                        _modeResult.En_MineName = Convert.ToString(_tabSource.Rows[0]["Kind_Name"].ToString());
                        _modeResult.En_Materiel = Convert.ToString(_tabSource.Rows[0]["Material_Id"].ToString());
                        _modeResult.En_MaterielName = Convert.ToString(_tabSource.Rows[0]["Material_Name"].ToString());
                        _modeResult.En_Supplier = Convert.ToString(_tabSource.Rows[0]["Supplier_Id"].ToString());
                        _modeResult.En_SupplierName = Convert.ToString(_tabSource.Rows[0]["Supplier_Name"].ToString());
                        _modeResult.En_Transport = Convert.ToString(_tabSource.Rows[0]["Unit_ID"].ToString());
                        _modeResult.En_TransportName = Convert.ToString(_tabSource.Rows[0]["Unit_Name"].ToString());
                        _modeResult.En_Sparestr1 = _tabSource.Rows[0]["Sparestr1"].ToString();
                        _modeResult.En_SamplingPosCount = 3;
                        _modeResult.En_SamplingLayout = 2;
                        _modeResult.En_Success = true;
                    }
                    else
                        _modeResult.En_ErrInfo = "获取到多条车辆与批次对应记录!系统无法自动匹配!";
                }
                else
                    _modeResult.En_ErrInfo = "未能获取到车辆与批次对应关系!";
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoSendRequest()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
            }
            return _modeResult;
        }

        /// <summary>
        /// 获取无人值守 车辆模拟信息
        /// </summary>
        /// <param name="carnum">车牌号码（湘A***）</param>
        /// <returns></returns>
        public Entity.SamplingInfo GetTestCarInfo(string carnum)
        {
            Entity.SamplingInfo _modeResult = new Entity.SamplingInfo();
            try
            {
                _strSql = string.Format(" select * from Test_CarInfo where  CarNum  = '{0}' ", carnum);
                System.Data.DataTable _tabSource = SQLiteHelper.GetDataTable(_strSql);
                if (_tabSource != null && _tabSource.Rows.Count > 0)
                {
                    _modeResult.En_Car = carnum;
                    _modeResult.En_TotleLength = _tabSource.Rows[0]["TotalLength"].ToDoubleEx();
                    _modeResult.En_TotleWidth = _tabSource.Rows[0]["TotalWidth"].ToDoubleEx(); //TotalWidth
                    _modeResult.En_Length = _tabSource.Rows[0]["CLength"].ToDoubleEx();
                    _modeResult.En_Width = _tabSource.Rows[0]["CWidth"].ToDoubleEx();
                    _modeResult.En_Height = _tabSource.Rows[0]["CHeight"].ToDoubleEx();
                    _modeResult.En_Postions = _tabSource.Rows[0]["Postion"].ToStringEx();
                    _modeResult.En_SamplingPosCount = 3;
                    _modeResult.En_SamplingLayout = 2;
                }
            }
            catch (Exception ex)
            {
                FormHelper.ApplicationShow(UI.MsgType.Information, $"读取车型模拟数据异常");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoSendRequest()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
            }
            return _modeResult;
        }

    }
}
